﻿namespace Cmn.Mis
{
    using Cmn;
    using System;
    using System.Collections;
    using System.Text.RegularExpressions;

    internal class SqlAnalyse
    {
        public static string AddSortField(string sql, string fieldName)
        {
            ArrayList selectLst = null;
            string str;
            sql = WipeSubSelect(sql, ref selectLst);
            Match match = Regex.Match(sql, @"\s+order\s+by\s+", RegexOptions.IgnoreCase);
            string str2 = " ";
            if (match.Success)
            {
                str = sql.Substring(0, match.Index + match.Length).Trim();
                string input = sql.Substring(match.Index + match.Length).Trim();
                string pattern = @"\s*" + fieldName + @"\s+desc\s*(,)?";
                if (Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase))
                {
                    input = Regex.Replace(input, pattern, " ", RegexOptions.IgnoreCase).Trim();
                }
                else
                {
                    pattern = Regex.Replace(input, @"\s*" + fieldName + @"\s*(asc)?\s*(,)?", " ", RegexOptions.IgnoreCase).Trim();
                    if (pattern != input)
                    {
                        str2 = " desc ";
                    }
                    input = pattern;
                }
                if (input == "")
                {
                    str = str + " " + fieldName + str2;
                }
                else
                {
                    string str6 = str;
                    str = str6 + " " + fieldName + str2 + "," + input;
                }
                str = Regex.Replace(str, @"\s*,\s*$", "", RegexOptions.IgnoreCase);
            }
            else
            {
                str = sql + " order by " + fieldName;
            }
            if (selectLst != null)
            {
                str = RecoverSubSelect(str, selectLst);
            }
            return str;
        }

        public static string AddTopToSelect(string sql)
        {
            if (!Regex.IsMatch(sql, @"^\s*select\s+(distinct\s+)?top\s+\d+\s+", RegexOptions.IgnoreCase))
            {
                Match match = Regex.Match(sql, @"^\s*select\s+(distinct\s+)?", RegexOptions.IgnoreCase);
                sql = Regex.Replace(sql, @"^\s*select\s+(distinct\s+)?", match.Value + " top 500 ", RegexOptions.IgnoreCase);
            }
            return sql;
        }

        public static string AddWhere(string sql, string where)
        {
            string str;
            if (where.Trim() == "")
            {
                return sql;
            }
            where = " (" + where + ") ";
            if (sql.IndexOf(where) != -1)
            {
                return sql;
            }
            ArrayList selectLst = null;
            sql = WipeSubSelect(sql, ref selectLst);
            int length = sql.ToLower().LastIndexOf(" where ");
            int num2 = sql.ToLower().LastIndexOf(" group ");
            int num3 = sql.ToLower().LastIndexOf(" order ");
            if (num2 < length)
            {
                num2 = -1;
            }
            if (num3 < length)
            {
                num3 = -1;
            }
            if (length == -1)
            {
                if (num2 == -1)
                {
                    if (num3 == -1)
                    {
                        str = sql + " where " + where;
                    }
                    else
                    {
                        str = sql.Substring(0, num3) + " where " + where + sql.Substring(num3);
                    }
                }
                else
                {
                    str = sql.Substring(0, num2) + " where " + where + sql.Substring(num2);
                }
            }
            else if (num2 == -1)
            {
                if (num3 == -1)
                {
                    str = sql.Substring(0, length) + " where (" + sql.Substring(length + 6) + ") and " + where;
                }
                else
                {
                    str = sql.Substring(0, length) + " where (" + sql.Substring(length + 6, (num3 - length) - 6) + ") and " + where + sql.Substring(num3);
                }
            }
            else
            {
                str = sql.Substring(0, length) + " where (" + sql.Substring(length + 6, (num2 - length) - 6) + ") and " + where + sql.Substring(num2);
            }
            if (selectLst != null)
            {
                str = RecoverSubSelect(str, selectLst);
            }
            return str;
        }

        public static string GetFullFieldName(string sql, string fieldName)
        {
            string str = fieldName;
            ArrayList selectLst = null;
            sql = WipeSubSelect(sql, ref selectLst);
            sql = Regex.Match(sql, @"\s*select[\s\S]*from\s*", RegexOptions.IgnoreCase).Value;
            Match match = Regex.Match(sql, @"\w+\." + fieldName + @"(?=,|\s+)", RegexOptions.IgnoreCase);
            if (match.Success)
            {
                str = match.Value.Trim();
            }
            else
            {
                match = Regex.Match(sql, @",\s*[^,]*(\s+as)?\s+" + fieldName + @"(?=\s*,|\s+)", RegexOptions.IgnoreCase);
                if (match.Success)
                {
                    sql = match.Value.Trim().Remove(0, 1).Trim();
                    if (sql.ToLower() != fieldName.Trim().ToLower())
                    {
                        sql = Regex.Replace(sql, @"(\s+as)?\s+" + fieldName + "$", "", RegexOptions.IgnoreCase).Trim();
                    }
                    str = sql;
                }
                else
                {
                    match = Regex.Match(sql, @"\s*select\s+(distinct\s+)?(top\s+\d+\s+)?[^,]*(\s+as\s+)?" + fieldName + @"(?=\s*,|\s+)", RegexOptions.IgnoreCase);
                    if (match.Success)
                    {
                        sql = match.Value.Trim();
                        sql = Regex.Replace(sql, @"\s*select\s+(distinct\s+)?(top\s+\d+\s+)?", "", RegexOptions.IgnoreCase).Trim();
                        if (sql.ToLower() != fieldName.Trim().ToLower())
                        {
                            sql = Regex.Replace(sql, @"(\s+as\s+)?" + fieldName + "$", "", RegexOptions.IgnoreCase).Trim();
                        }
                        str = sql;
                    }
                }
            }
            if (selectLst != null)
            {
                str = RecoverSubSelect(str, selectLst);
            }
            return str;
        }

        public static string GetFullSortFieldName(string sql, string fieldName)
        {
            string input = GetFullFieldName(sql, fieldName).Trim();
            if (!Regex.IsMatch(input, @"^[\w\.]+$", RegexOptions.IgnoreCase))
            {
                input = fieldName;
            }
            return input;
        }

        public static string GetKeyField(string sql)
        {
            return Regex.Replace(Regex.Match(sql, @"\s*select\s+(distinct\s+)?(top\s+\d+\s+)?[\w\.]+(?=,|\s+)", RegexOptions.IgnoreCase).Value.Trim(), @"\s*select\s+(distinct\s+)?(top\s+\d+\s+)?(\w+\.|)", "", RegexOptions.IgnoreCase).Trim();
        }

        public static string GetMainTableName(string sql)
        {
            ArrayList selectLst = null;
            sql = WipeSubSelect(sql, ref selectLst);
            string input = Regex.Match(sql, @"\s+from\s+\S+(?=\s+|,)", RegexOptions.IgnoreCase).Value.Trim();
            if (input.Trim() == "")
            {
                input = Regex.Match(sql, @"\s+from\s+\S+$", RegexOptions.IgnoreCase).Value.Trim();
            }
            return Regex.Replace(input, @"\s*from\s+", "", RegexOptions.IgnoreCase).Trim();
        }

        public static int GetRecCount(string sql)
        {
            ArrayList selectLst = null;
            sql = WipeSubSelect(sql, ref selectLst);
            string fullFieldName = GetFullFieldName(sql, GetKeyField(sql));
            string str2 = Regex.Replace(Regex.Replace(sql, @"\s*select[\s\S]*from\s*", "select count(" + fullFieldName + ") from ", RegexOptions.IgnoreCase), @"\s*order\s*by[\s\S]*", " ", RegexOptions.IgnoreCase);
            if (selectLst != null)
            {
                str2 = RecoverSubSelect(str2, selectLst);
            }
            str2 = DB.getFieldValue(str2).Trim();
            if (str2 != "")
            {
                return Convert.ToInt32(str2);
            }
            return -1;
        }

        private static string RecoverSubSelect(string sql, ArrayList selectLst)
        {
            for (int i = 0; i < selectLst.Count; i++)
            {
                sql = sql.Replace("#" + (i + 1) + "#", selectLst[i].ToString());
            }
            return sql;
        }

        private static string WipeSubSelect(string sql, ref ArrayList selectLst)
        {
            Match match = Regex.Match(sql, @"\(\s*select\s+", RegexOptions.IgnoreCase);
            int num = 1;
            selectLst = null;
            while (match.Success)
            {
                int num2 = 1;
                for (int i = match.Index + 1; i < sql.Length; i++)
                {
                    if (sql[i] == '(')
                    {
                        num2++;
                    }
                    else if (sql[i] == ')')
                    {
                        num2--;
                    }
                    if (num2 == 0)
                    {
                        if (num == 1)
                        {
                            selectLst = new ArrayList();
                        }
                        selectLst.Add(sql.Substring(match.Index + 1, (i - match.Index) - 1));
                        sql = sql.Remove(match.Index + 1, (i - match.Index) - 1);
                        sql = sql.Insert(match.Index + 1, "#" + num + "#");
                        num++;
                        break;
                    }
                }
                match = Regex.Match(sql, @"\(\s*select\s+", RegexOptions.IgnoreCase);
            }
            return sql;
        }
    }
}

